﻿
<!DOCTYPE html>
@{  
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Overall stats";
    
    string hittingWhereClause = null;
    string pitchingWhereClause = null;
    var preSeasonIncluded = true;
    var ourScore = 0;
    var opponentScore = 0;
    var opponentName = "";
    string opponentDate = "";
    
    if(!string.IsNullOrEmpty(Request.QueryString["Last3"]))
    {
        hittingWhereClause = " WHERE HittingStats.GameId IN (SELECT TOP 3 Games.Id FROM Games WHERE OurScore IS NOT NULL ORDER BY Games.Id DESC) ";
        pitchingWhereClause = " WHERE PitchingStats.GameId IN (SELECT TOP 3 Games.Id FROM Games WHERE OurScore IS NOT NULL ORDER BY Games.Id DESC) ";   
        Page.Title = "Stats for the last 3 games";
    }
    
    var db = Database.Open("ICRams");
    
    if(!string.IsNullOrEmpty(Request.QueryString["GameId"])){
        hittingWhereClause = " WHERE HittingStats.GameId = " + Request.QueryString["GameId"];
        pitchingWhereClause = " WHERE PitchingStats.GameId = " + Request.QueryString["GameId"];      
            
        var games = db.Query(@"SELECT TOP 1 Games.Opponent, Games.OurScore, Games.OpponentScore, Games.GameDate, Games.HomeAway FROM Games WHERE Games.Id = " +  Request.QueryString["GameId"]);
        var game = games.FirstOrDefault();
        if(game != null){
            Page.Title = "Stats " + game.HomeAway + " " + game.Opponent + " on " + game.GameDate.ToShortDateString();
            ourScore = game.OurScore ?? 0;
            opponentName = game.Opponent;
            opponentScore = game.OpponentScore ?? 0;
            opponentDate = game.GameDate.ToShortDateString();
        }
        else{
            db.Close();
            Response.Write("Invalid GameId");
            Response.End();
        }
    }
        
    if(string.IsNullOrEmpty(Request.QueryString["IncludePreSeason"]) || Request.QueryString["IncludePreSeason"] == "False"){
        //Don't override last 3 or individual game where clause
        hittingWhereClause = hittingWhereClause ?? " INNER JOIN Games On HittingStats.GameId = Games.Id WHERE Games.PreSeason = 0 " ;
        pitchingWhereClause = pitchingWhereClause ?? " INNER JOIN Games On PitchingStats.GameId = Games.Id WHERE Games.PreSeason = 0 " ;   
        preSeasonIncluded = false;
    }
    
    var hittingQuerySelect = @"SELECT DisplayName,  SUM(HittingStats.HomeRuns) AS HR, SUM(HittingStats.Triples) AS [Triples], 
    SUM(HittingStats.Doubles) AS [Doubles], SUM(HittingStats.Singles) AS [Singles], 
    SUM(HittingStats.HomeRuns + HittingStats.Triples + HittingStats.Doubles + HittingStats.Singles) AS Hits,
    SUM(HittingStats.Walks) AS BB,  
    SUM(HittingStats.StrikeoutsSwinging) AS SOs, SUM(HittingStats.StrikeoutsLooking) AS SOl, SUM(HittingStats.GroundOuts) AS GO,  SUM(HittingStats.FlyOuts) AS FO,  
    SUM(HittingStats.ReachOnError) AS ROE,  SUM(HittingStats.Sacrifices) AS SAC,  SUM(HittingStats.HitByPitch) AS HBP, SUM(HittingStats.RBI) AS RBI,
    SUM(HittingStats.StolenBases) AS SB, SUM(HittingStats.Runs) AS R,
    SUM(HittingStats.StrikeoutsSwinging)  + SUM(HittingStats.StrikeoutsLooking) + SUM(HittingStats.HomeRuns) + SUM(HittingStats.Triples) + SUM(HittingStats.Doubles) + SUM(HittingStats.Singles) + SUM(HittingStats.Walks) + SUM(HittingStats.GroundOuts)  +  SUM(HittingStats.FlyOuts)  + SUM(HittingStats.ReachOnError) + SUM(HittingStats.Sacrifices)  + SUM(HittingStats.HitByPitch)  AS AB, 
    SUM(HittingStats.HomeRuns) + SUM(HittingStats.Triples) + SUM(HittingStats.Doubles) + SUM(HittingStats.Singles) + SUM(HittingStats.StrikeoutsSwinging)  + SUM(HittingStats.StrikeoutsLooking) + SUM(HittingStats.GroundOuts)  +  SUM(HittingStats.FlyOuts)  + SUM(HittingStats.ReachOnError) + SUM(HittingStats.Sacrifices)  AS AtBatsCountingForAvg ,
    SUM(HittingStats.StrikeoutsSwinging)  + SUM(HittingStats.StrikeoutsLooking) + SUM(HittingStats.GroundOuts)  +  SUM(HittingStats.FlyOuts)  + SUM(HittingStats.ReachOnError)  + SUM(HittingStats.Sacrifices) AS OutsForAverage,
    SUM(HittingStats.StrikeoutsSwinging)  + SUM(HittingStats.StrikeoutsLooking) + SUM(HittingStats.GroundOuts)  +  SUM(HittingStats.FlyOuts) + SUM(HittingStats.Sacrifices) AS Outs    
    From Players LEFT OUTER JOIN HittingStats ON Players.Id = HittingStats.PlayerId ";
    
    var hittingQuery = hittingQuerySelect + hittingWhereClause + " GROUP BY DisplayName  ORDER BY Hits DESC";
        
    var players =
        db.Query(hittingQuery);
    
    if(players==null || players.Count() ==0)
    {
        db.Close();
        Response.Write(String.Format("No Stats Entered yet for the game vs {0} on {1}", opponentName, opponentDate));
        Response.End();
    }
    
    var hiHomeRuns = players.Select(p => p.HR).Max();
    var hiTriples = players.Select(p => p.Triples).Max();
    var hiDoubles = players.Select(p => p.Doubles).Max();
    var hiSingles = players.Select(p => p.Singles).Max();
    var hiWalks = players.Select(p => p.BB).Max();
    var hiStrikeoutsSwinging = players.Select(p => p.SOs).Max();
    var hiStrikeoutsLooking = players.Select(p => p.SOl).Max();
    var hiRBI = players.Select(p => p.RBI).Max();
    var hiGroundOuts = players.Select(p => p.GO).Max();
    var hiFlyOuts = players.Select(p => p.FO).Max();
    var hiSacrifice = players.Select(p => p.SAC).Max();
    var hiHitByPitch = players.Select(p => p.HBP).Max();
    var hiReachOnError = players.Select(p => p.ROE).Max();
    var hiStolenBase = players.Select(p => p.SB).Max();
    var hiRun = players.Select(p => p.R).Max();
    var hiOuts = players.Select(p => p.Outs).Max();
    var hiAB = players.Select(p => p.AB).Max();
    var hiAvg = String.Format("{0:.000}", players.Where(p => p.AB > 0).Select(p => ((double)(p.AtBatsCountingForAvg-p.OutsForAverage)/p.AtBatsCountingForAvg)).Max());
    var hiOBPct = String.Format("{0:.000}", players.Where(p => p.AB > 0).Select(p => ((double)(p.AB-p.Outs)/p.AB)).Max());
   
    var pitchers = db.Query(@"SELECT DisplayName, SUM(PitchingStats.HomeRuns) AS HR, SUM(PitchingStats.Triples) AS [Triples], 
    SUM(PitchingStats.Doubles) AS [Doubles], SUM(PitchingStats.Singles) AS [Singles], SUM(PitchingStats.StrikeOuts) AS K,
    SUM(PitchingStats.Walks) AS BB,    SUM(PitchingStats.HitBatter) AS HBP,    SUM(PitchingStats.Error) As E,
    SUM(PitchingStats.InningsPitched) AS IP,SUM(PitchingStats.EarnedRuns) AS ER,    SUM(PitchingStats.TotalRuns) AS R,   
    SUM(PitchingStats.Balls) AS B, SUM(PitchingStats.Strikes) AS S, SUM(PitchingStats.BattersFaced) AS AB, SUM(PitchingStats.FirstPitchStrikes) AS FPS,
    SUM(PitchingStats.FieldOuts) AS O,
    SUM(PitchingStats.Balls + PitchingStats.Strikes + PitchingStats.HomeRuns + PitchingStats.Triples + PitchingStats.Doubles + PitchingStats.Singles + PitchingStats.HitBatter + PitchingStats.Error + PitchingStats.FieldOuts) AS TP,
    CASE SUM(PitchingStats.EarnedRuns)
        WHEN 0 THEN 0
        ELSE SUM(PitchingStats.EarnedRuns)/SUM(PitchingStats.InningsPitched)*6
        END AS ERA
    From Players LEFT OUTER JOIN PitchingStats ON Players.Id = PitchingStats.PlayerId " + pitchingWhereClause + @" GROUP BY DisplayName HAVING Count(PitchingStats.BattersFaced) > 0
     ORDER BY ERA");
    
    var hiK = pitchers.Select(p => p.K).Max();
    var hiHR = pitchers.Select(p => p.HR).Max();
    var hi3B = pitchers.Select(p => p.Triples).Max();
    var hi2B = pitchers.Select(p => p.Doubles).Max();
    var hi1B = pitchers.Select(p => p.Singles).Max();
    var hiBB = pitchers.Select(p => p.BB).Max();
    var hiHBP = pitchers.Select(p => p.HBP).Max();
    var hiE = pitchers.Select(p => p.E).Max();
    var hiIP = pitchers.Select(p => p.IP).Max();
    var hiER = pitchers.Select(p => p.ER).Max();
    var hiR = pitchers.Select(p =>p.R).Max();
    var hiO = pitchers.Select(p => p.O).Max();
    var hiTP = pitchers.Select(p =>p.TP).Max();
    var lowERA = pitchers.Select(p =>p.ERA).Min();
    var hiB = pitchers.Select(p =>p.B).Max();
    var hiS = pitchers.Select(p =>p.S).Max();
    var hiABp = pitchers.Select(p =>p.AB).Max();
    var hiFPS = pitchers.Select(p =>p.FPS).Max();

    var hiFPSPct = pitchers.Count()>0 ? String.Format("{0:.000}", pitchers.Where(p => p.TP > 0).Select(p => ((double)p.FPS/p.AB)).Max()) : 0;
        
    db.Close();
    var sumHR = 0;
    int sumTriples=0;
    int sumDoubles=0;
    int sumSingles=0;
    int sumBB = 0;
    int sumRBI = 0;
    int sumSOS = 0;
    int sumSOL = 0;
    int sumGO = 0;
    int sumFO = 0;
    int sumSAC = 0;
    int sumHBP = 0;
    int sumROE = 0;
    int sumSB = 0;
    int sumR = 0;
    int sumOUT = 0;
    int sumAB = 0;
    int sumAVG = 0;
    int sumOBP = 0;
}

    
    @if(string.IsNullOrEmpty(Request.QueryString["Last3"]) && string.IsNullOrEmpty(Request.QueryString["GameId"])){ 
    <a href="?IncludePreSeason=@(preSeasonIncluded==false)" style="color:#ff0000;font-weight:bold;">
        <span>
            @if(@preSeasonIncluded){
                <span>Exclude Pre-Season</span>
            }else{
                <span>Include Pre-Season</span>
            } 
       </span>
    </a>
    }
@if(!string.IsNullOrEmpty(opponentName)){
    <span>IC: </span><span class="score">@ourScore</span><span> - @opponentName : </span><span class="score">@opponentScore</span>
    }
<p>
    <table>
        <tr bgcolor="#ffff00"><td>Player</td><td>AVG</td><td>AB</td><td>HR</td><td>3B</td><td>2B</td><td>1B</td><td>SOs</td><td>SOl</td><td>BB</td><td>RBI</td><td>GO</td><td>FO</td><td>FC</td><td>HBP</td><td>ROE</td><td>SB</td><td>R</td><td>Outs</td><td>OB%</td></tr>
        @foreach(var player in players){
            var hrAsterick = "";
            var tripleAsterick = "";
            var doubleAsterick = "";
            var singleAsterick = "";
            var bbAsterick = "";
            var rbiAsterick = "";
            var sosAsterick = "";
            var solAsterick = "";
            var goAsterick = "";
            var foAsterick = "";
            var sacAsterick = "";
            var hbpAsterick = "";
            var roeAsterick = "";
            var sbAsterick = "";
            var rAsterick = "";
            var outAsterick = "";
            var abAsterick = "";
            var avgAsterick = "";
            var obPctAsterick = "";
            string myAverage = String.Format("{0:.000}", player.AtBatsCountingForAvg > 0 ? (double)(@player.AtBatsCountingForAvg-@player.OutsForAverage)/@player.AtBatsCountingForAvg : 0.000);
            string myOBPct =  String.Format("{0:.000}", player.AB > 0 ? (double)(@player.AB-@player.Outs)/@player.AB : 0.000);
            
              if(@player.HR == hiHomeRuns){hrAsterick =  "*";};
            sumHR += @player.HR;
              if(@player.Triples == hiTriples){tripleAsterick =  "*";};
            sumTriples += @player.Triples;
              if(@player.Doubles == hiDoubles){doubleAsterick =  "*";};
            sumDoubles += @player.Doubles;
              if(@player.Singles == hiSingles){singleAsterick =  "*";};
            sumSingles += @player.Singles;
              if(@player.BB == hiWalks){bbAsterick =  "*";};
            sumBB += @player.BB;
              if(@player.RBI == hiRBI){rbiAsterick =  "*";}
            sumRBI += @player.RBI;
              if(@player.SOs == hiStrikeoutsSwinging){sosAsterick =  "*";};
            sumSOS += @player.SOs;
              if(@player.SOl == hiStrikeoutsLooking){solAsterick =  "*";};
            sumSOL += @player.SOl;
              if(@player.GO == hiGroundOuts){goAsterick =  "*";};
            sumGO += @player.GO;
              if(@player.FO == hiFlyOuts){foAsterick =  "*";};
            sumFO += @player.FO;
              if(@player.SAC == hiSacrifice){sacAsterick =  "*";};
            sumSAC += @player.SAC;
              if(@player.HBP == hiHitByPitch){hbpAsterick =  "*";};
            sumHBP += @player.HBP;
              if(@player.ROE == hiReachOnError){roeAsterick =  "*";};
            sumROE += @player.ROE;
              if(@player.SB == hiStolenBase){sbAsterick = "*";};
            sumSB += @player.SB;
              if(@player.R == hiRun){rAsterick = "*";};
            sumR += @player.R;
              if(@player.Outs == hiOuts){outAsterick =  "*";};
            sumOUT += @player.Outs;
              if(@player.AB == hiAB){abAsterick =  "*";};
            sumAB += @player.AB;
              if(myAverage == hiAvg){avgAsterick =  "*";};
            //sumAVG += myAverage;
              if(myOBPct == hiOBPct){obPctAsterick =  "*";};
            //sumOBP += myOBP;
               
            <tr><td cellpadding="0" cellspacing="0">@player.DisplayName</td>
                <td>@myAverage<span style="color:#ff0000">@avgAsterick</span></td>
                <td>@player.AB<span style="color:#ff0000">@abAsterick</span></td>
                <td>@player.HR<span style="color:#ff0000">@hrAsterick</span></td>
                <td>@player.Triples<span style="color:#ff0000">@tripleAsterick</span></td>
                <td>@player.Doubles<span style="color:#ff0000">@doubleAsterick</span></td>
                <td>@player.Singles<span style="color:#ff0000">@singleAsterick</span></td>
                <td>@player.SOs<span style="color:#ff0000">@sosAsterick</span></td>
                <td>@player.SOl<span style="color:#ff0000">@solAsterick</span></td>
                <td>@player.BB<span style="color:#ff0000">@bbAsterick</span></td>
                <td>@player.RBI<span style="color:#ff0000">@rbiAsterick</span></td>
                <td>@player.GO<span style="color:#ff0000">@goAsterick</span></td>
                <td>@player.FO<span style="color:#ff0000">@foAsterick</span></td>
                <td>@player.SAC<span style="color:#ff0000">@sacAsterick</span></td>
                <td>@player.HBP<span style="color:#ff0000">@hbpAsterick</span></td>
                <td>@player.ROE<span style="color:#ff0000">@roeAsterick</span></td>
                <td>@player.SB<span style="color:#ff0000">@sbAsterick</span></td>
                <td>@player.R<span style="color:#ff0000">@rAsterick</span></td>
                <td>@player.Outs<span style="color:#ff0000">@outAsterick</span></td>
                <td>@myOBPct<span style="color:#ff0000">@obPctAsterick</span></td>
                                
                </tr>
        }
        <tr bgcolor="#eeeeee"><td cellpadding="0" cellspacing="0"></td>
                <td></td>
                <td>@sumAB</td>
                <td>@sumHR</td>
                <td>@sumTriples</td>
                <td>@sumDoubles</td>
                <td>@sumSingles</td>
                <td>@sumSOS</td>
                <td>@sumSOL</td>
                <td>@sumBB</td>
                <td>@sumRBI</td>
                <td>@sumGO</td>
                <td>@sumFO</td>
                <td>@sumSAC</td>
                <td>@sumHBP</td>
                <td>@sumROE</td>
                <td>@sumSB</td>
                <td>@sumR</td>
                <td>@sumOUT</td>
                <td></td>
                                
                </tr>
    </table>
    <div class="legend">SOs-Strikeouts Swinging, SOl-Strikeouts Looking, BB-Base On Balls, RBI-Runs Batted In, GO-Ground Out, FO-Fly Out, SAC-Sacrifice, HBP-Hit By Pitch, ROE-Reached On Error, SB-Stolen Bases, R-Runs, O-Outs(Total), OB%-On Base Percentage</div>

    <h4>Pitching:</h4>
        <table>
        <tr bgcolor="#ffaaff"><td>Player</td><td>ERA</td><td>K</td><td>BB</td><td>HR</td><td>3B</td><td>2B</td><td>1B</td><td>HBP</td><td>E</td><td>IP</td><td>ER</td><td>R</td><td>TP</td><td>S</td><td>B</td><td>AB</td><td>O</td><td>FPS</td><td>FPS%</td></tr>
        @foreach(var pitcher in pitchers){
            var eraAsterick = "";
              if(@pitcher.ERA == lowERA){eraAsterick =  "*";};
            var bbAsterick = "";
              if(@pitcher.BB == hiBB){bbAsterick =  "*";};
            var kAsterick = "";
              if(@pitcher.K == hiK){kAsterick =  "*";};
            var hbpAsterick = "";
              if(@pitcher.HBP == hiHBP){hbpAsterick =  "*";};
            var eAsterick = "";
              if(@pitcher.E == hiE){eAsterick =  "*";};
            var ipAsterick = "";
              if(@pitcher.IP == hiIP){ipAsterick =  "*";};
            var erAsterick = "";
              if(@pitcher.ER == hiER){erAsterick =  "*";};
            var rAsterick = "";
              if(@pitcher.R == hiR){rAsterick =  "*";};
            var oAsterick = "";
              if(@pitcher.O == hiO){oAsterick =  "*";};
            var tpAsterick = "";
              if(@pitcher.TP == hiTP){tpAsterick =  "*";};
            var sAsterick = "";
                if(@pitcher.S == hiS){sAsterick = "*";};
            var bAsterick = "";
                if(@pitcher.B == hiB){bAsterick = "*";};
            var abAsterick = "";
                if(@pitcher.AB == hiABp){abAsterick = "*";};
            var fpsAsterick = "";
                if(@pitcher.FPS == hiFPS){fpsAsterick = "*";};
                
            string myFPSPct =  String.Format("{0:.000}", pitcher.AB > 0 ? (double)(@pitcher.FPS)/@pitcher.AB : 0.000);
            var fpsPctAsterick = "";
                if(myFPSPct == hiFPSPct){ fpsPctAsterick = "*"; };
                
            var hrAsterick = "";
            var tripleAsterick = "";
            var doubleAsterick = "";
            var singleAsterick = "";
              if(@pitcher.HR == hiHR){hrAsterick =  "*";};
              if(@pitcher.Triples == hi3B){tripleAsterick =  "*";};
              if(@pitcher.Doubles == hi2B){doubleAsterick =  "*";};
              if(@pitcher.Singles == hi1B){singleAsterick =  "*";};
            
            <tr>
                <td cellpadding="0" cellspacing="0">@pitcher.DisplayName</td>
                <td>@String.Format("{0:.000}", pitcher.ERA)<span style="color:#ff0000">@eraAsterick</span></td>
                <td>@pitcher.K<span style="color:#ff0000">@kAsterick</span></td>
                <td>@pitcher.BB<span style="color:#ff0000">@bbAsterick</span></td>
                <td>@pitcher.HR<span style="color:#ff0000">@hrAsterick</span></td>
                <td>@pitcher.Triples<span style="color:#ff0000">@tripleAsterick</span></td>
                <td>@pitcher.Doubles<span style="color:#ff0000">@doubleAsterick</span></td>
                <td>@pitcher.Singles<span style="color:#ff0000">@singleAsterick</span></td>
                <td>@pitcher.HBP<span style="color:#ff0000">@hbpAsterick</span></td>
                <td>@pitcher.E<span style="color:#ff0000">@eAsterick</span></td>
                <td>@String.Format("{0:.000}", pitcher.IP)<span style="color:#ff0000">@ipAsterick</span></td>
                <td>@pitcher.ER<span style="color:#ff0000">@erAsterick</span></td>
                <td>@pitcher.R<span style="color:#ff0000">@rAsterick</span></td>
                <td>@pitcher.TP<span style="color:#ff0000">@tpAsterick</span></td>
                <td>@pitcher.S<span style="color:#ff0000">@sAsterick</span></td>
                <td>@pitcher.B<span style="color:#ff0000">@bAsterick</span></td>
                <td>@pitcher.AB<span style="color:#ff0000">@abAsterick</span></td>
                <td>@pitcher.O<span style="color:#ff0000">@oAsterick</span></td>
                <td>@pitcher.FPS<span style="color:#ff0000">@fpsAsterick</span></td>
                <td>@myFPSPct<span style="color:#ff0000">@fpsPctAsterick</span></td>
            </tr>        }
    </table>
    <div class="legend">ERA-Earned Run Average, K-Strikeouts, BB-Base On Balls, HR-Home Run, HBP-Hit By Pitch, E-Errors, IP-Innings Pitched, ER-Earned Runs, R-Runs, TP-Total Pitches, S-Strikes thrown, B-Balls Thrown, AB-At Bats, O-Outs(in field), FPS-First Pitch Strikes, FPS%-First Pitch Strike Percentage</div>
</p>